package org.jiangnantaste.daoimpl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.jiangnantaste.dao.ImageDao;
import org.jiangnantaste.database.DBConf;
import org.jiangnantaste.domain.Image;

public class ImageDaoImpl implements ImageDao {

    
    @Override
    public List<Image> listAllImage(Connection connection) throws Exception{
        String query;

        query = "SELECT * FROM image";
        
        ArrayList<Image> imageData = new ArrayList<Image>();

        ResultSet result = connection.prepareStatement(query).executeQuery();

        while(result.next()) {            
        	//Build the image object
            Image image = new Image();
            image.setImageId(result.getLong("image_id"));
            image.setImageName(result.getString("image_name"));
            image.setPathName(result.getString("path_name"));
            image.setActive(result.getBoolean("active"));
            image.setImageData(result.getBytes("image_data"));

            //Build the object list
            imageData.add(image);
        }
        return imageData;
    } 

    @Override
    public boolean updateImage(Connection connection,Image image) throws Exception {
	    String query = "";
	    String query0 = "";
	    int count = 0;
	
	    try {
	        if(image!=null) {
	            query0 = "SELECT count(*) as countNum FROM image where image_id=?;";
	            PreparedStatement ps0 = connection.prepareStatement(query0);
	            ps0.setLong(1, image.getImageId());
	            ResultSet result0 = connection.prepareStatement(query0).executeQuery();
	            if (result0.next()){
	            	count = result0.getInt("countNum");
	            }
	            
	            if (count == 0) {
	                return false;
	            }
	            else {
	                query = "update image set image_name=?,path_name=?,active=?,image_data=? where image_id=?;";
	                PreparedStatement ps = connection.prepareStatement(query);
	                ps.setString(1,image.getImageName());
	                ps.setString(2, image.getPathName());
	                ps.setBoolean(3, image.isActive());
	                ps.setBytes(4, image.getImageData());
	                ps.setLong(5, image.getImageId());
	                connection.prepareStatement(query).executeUpdate();
	                return true;
	            }
	        }
	     } catch(SQLException e){
	         throw new IOException(e.getMessage());
	     }
	    return false;
    }

	@Override
	public boolean addImage(Connection connection, Image image) throws Exception {		
	    String query = "";
	    String query0 = "";
	    int count = 0;
	    long imageid = image.getImageId();
	
	    try {
	    		if (imageid > 0){
	    			query0 = "SELECT count(*) as countNum FROM image where image_id=?;";	    		
		            PreparedStatement ps0 = connection.prepareStatement(query0);
		            ps0.setLong(1, image.getImageId());
		            ResultSet result0 = connection.prepareStatement(query0).executeQuery();
		            if (result0.next()){
		            	count = result0.getInt("countNum");
		            }	            
	    		} else {
	    			count = 0;
	    		}
	            
	            if (count > 0) {
	                return false;
	            }
	            else {
	                query = "insert into image (image_name ,path_name ,active,image_data) values (?,?,?,?);";
	                PreparedStatement ps = connection.prepareStatement(query);
	                ps.setString(1,image.getImageName());
	                ps.setString(2, image.getPathName());
	                ps.setBoolean(3, image.isActive());
	                ps.setBytes(4, image.getImageData());
	                ps.executeUpdate();
	                return true;
	        }
	     } catch(SQLException e){
	         throw new IOException(e.getMessage());
	     }
	}

	@Override
	public boolean deleteImage(Connection connection,long imageid) throws Exception {
	    String query = "";
	    int result = 0;
	    boolean retVal = false;
	
	    try {
            query = "delete from image where image_id=?;";
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setLong(1, imageid);
            result = ps.executeUpdate();
            if (result > 0) {
            	retVal = true;
            } else {
            	retVal = false;
	    	}
	     } catch(SQLException e){
	         throw new IOException(e.getMessage());
	     } catch(Exception e){
	         throw new IOException(e.getMessage());
	     }
	     return retVal;
	}

	@Override
	public Image findImageByImageId(Connection connection, long imageid) throws Exception {
		String query;

		query = "SELECT * FROM image WHERE image_id=?";

		Image image = new Image();
		PreparedStatement ps = connection.prepareStatement(query);
		ps.setLong(1, imageid);
		ResultSet result = ps.executeQuery();

		while (result.next()) {
			// Build the image object

			image.setImageId(result.getLong("image_id"));
			image.setImageName(result.getString("image_name"));
			image.setPathName(result.getString("path_name"));
			image.setActive(result.getBoolean("active"));
			image.setImageData(result.getBytes("image_data"));
		}
		return image;
	}
}
